The Crystal SQL Designer Help Main Index

  Click the area of interest on the picture below for further information:

Crystal SQL Designer Overview Topics

Create SQL Expert

Crystal SQL Designer Commands Index

Dialog Box Index

SQL Topics Index

Hands-On Tutorial Topics


Crystal SQL Designer Overview Topics

  Click the area of interest on the picture below for further information:

The Crystal SQL Designer

Why Use A Query?

Using the SQL Designer

  The Crystal SQL Designer Help Index


OVERVIEW TOPICS


The Crystal SQL Designer

The Crystal SQL Designer is a type of query; a request for specific information from a database. If you are requesting that information from an SQL database (or from a database accessed via ODBC), your query must be written using the Structured Query Language (SQL). The SQL language is not difficult to learn, but mastering the fine points of creating and retrieving data with SQL can take quite a while. Since the SQL Designer eliminates the need to understand SQL, it can get you building effective queries right away.

The SQL Designer has been designed to meet the needs of individuals with little or no query background, as well as, the needs of experienced SQL professionals.

•	If you are new to querying, you will enjoy the way the SQL Designer helps you create queries. By answering a few questions on a set of sequential tabs, you'll give the program all the information needed to generate a query that fits your needs. The program will do all the work for you.

•	If you are an SQL professional, you will appreciate the facility that enables you to fine-tune the queries that the SQL Designer generates. If you are more comfortable writing your own SQL queries, you will find it easy to enter the queries directly or even paste them in from another source.

The Crystal SQL Designer can be a powerful tool for many of your information gathering needs.

	The SQL Designer can only access data stored in an ODBC data source. Any SQL or other type of database mentioned in the query topics must be accessed through an ODBC data source.
  Crystal SQL Designer Overview Topics


Why Use A Query?

Query files provide a means of off-loading much of the data generating tasks normally performed by Crystal Reports on to an SQL or other database server. The set of data returned represents a subset of the actual data in the database - only the data you specifically need.

If you are an experienced SQL programmer, the Crystal SQL Designer allows you to reuse your existing queries, quickly and easily. All of your existing queries become convenient query files that you can use to design reports with Crystal Reports.

In addition, a Query file provides full ANSI SQL compatibility. Although Crystal Reports supports powerful SQL pass-through reporting, it does not support the full SQL language or allow you to edit every part of an SQL statement. When using the SQL Designer to create data sets on which to base your reports, you get all of the power of SQL, including complex joins, sorts, and aggregate functions.

	Many of the topics refer to specific SQL clauses. If you are experienced with SQL, use this information to better understand how the Query Expert generates an SQL statement. If you are new to SQL, you can ignore this information and continue to use the Query Expert.
  Crystal SQL Designer Overview Topics


Using the SQL Designer

The SQL Designer has two primary uses:

1.	Designing and developing data sets for building reports in Crystal Reports, and

2.	Retrieving and analyzing current information on an "as needed" basis to facilitate informed decision making.

Crystal Reports And Query Data Sets

Crystal Reports allows you to design a report based on a query data set rather than tables and fields. Since the query contains a predefined set of data, the tables and fields necessary for the report are already included.

The set of data produced by the SQL query works just like a database table when you design your report. The name of the query, along with the fields it accesses, appears in the Insert Field dialog box. Aggregate functions and SQL expressions act like fields, providing data values corresponding to each record in the SQL query.

Data Analysis And Decision Making

You may not always need to produce finished reports based on your data; sometimes you just need numbers in a hurry (to prepare for a meeting, to help make projections, etc.). The SQL Designer makes it easy to get the information you need.

Sometimes you may need to retrieve the same data on a recurring basis (weekly, every month end, etc.). By setting up and saving a single query, you can retrieve updated data quickly, whenever you need it, with minimal effort.

	The SQL Designer can not retrieve any number value larger than 20 characters or calculate a formula with a value larger than 20 characters. Values larger than 20 characters will be truncated.
  Crystal SQL Designer Overview Topics


Hands-On Tutorial Topics

  Click the tutorial of interest from the following list for further information:

How to use an SQL query that you designed elsewhere

How to create a new query

How to add tables

How to link tables and specify a join type

How to add field(s)

How to identify unique values

How to summarize data with aggregate functions

How to sort records according to field values

How to specify records to be included

How to select groups to be included

How to create an SQL expression

How to select a query for a report

How to create a query from another Crystal Query

How to create a parameter field for use in a query

How to use a parameter field in a query

  The Crystal SQL Designer Help Main Index


HANDS-ON TUTORIALS


How to use an SQL query that you designed elsewhere

1	Copy the SQL statement to the Windows Clipboard. Many SQL editors allow the SQL statement to be copied to the Clipboard using the Ctrl-C key combination.

2	Click the New button on the toolbar in the Crystal SQL Designer.

The New Query dialog box appears.

3	Click the Enter SQL statement directly button.

The Log On Server dialog box appears.

4	Select the desired SQL server for your SQL statement, and click OK.

The Log On Server dialog box appears.

5	Enter your user ID and password to log on to the SQL server, and click OK.

After a message appears indicating the success of your log on (assuming you logged on correctly) the Enter SQL Statement dialog box appears.

The SQL edit box allows you to make any changes you wish to the SQL statement. You can even enter an entirely new SQL statement if you wish.

6	Select an ODBC data source from the Data Source drop-down list.

7	To log on to another server or ODBC data source, click the Log On Server button. All data sources that you are logged on to will appear in the Data Source drop-down list.

8	In the Title text box, type a descriptive title for the query.

9	Enter the SQL statement in the large SQL edit box. Click anywhere inside the box to place an insertion point.

10	Press Ctrl-V or click the Paste button in the dialog box to paste the SQL statement into the SQL edit box.

11	Make any changes you need and click the OK button when you are finished.

The old SQL statement becomes a new SQL query that can be used with Crystal Reports just like any other SQL query file.

Alternatively, you can import an SQL statement saved in an ASCII text file. To do so, disregard Step 1 above, follow Steps 2 through 6 to open the Enter SQL Statement dialog box and log on to the ODBC data source, then click Import to import the SQL statement from the text file.

  Hands-On Tutorial Topics


How to create a new query

1	Click the New button in the Crystal SQL Designer.

The New Query dialog box appears.

2	Click the Use SQL Expert button (New Query dialog box).

The Create SQL Expert consists of several tabs. The tabs are numbered to lead you step-by-step through the query creation process.

	The Links Tab only appears when two or more database tables are selected on the Tables Tab.
	Once you select at least one field for the query on the Fields Tab, you can view the data retrieved by your query at any time, by clicking the Preview Query button at the bottom of the dialog box. After you are done viewing the query data, use the Edit button on the standard toolbar to return to the Create SQL Expert.
  Hands-On Tutorial Topics


How to add tables

FROM clause

The FROM clause specifies the sources (tables) of the database fields indicated in the SELECT statement.

1	Click the Tables Tab in the Create SQL Expert.

2	Click the SQL/ODBC or Dictionary button.

•	If you click SQL/ODBC:

—	Select an SQL or ODBC data source in the Log On Server dialog box.

—	Choose one or more database tables in the Choose SQL Table dialog box. Click Add to add each table to your SQL query. Click Done when finished.

•	If you click Dictionary:

—	Select Dictionary (.DC5) in the File Open dialog box, and click OK.

—	All ODBC database tables accessible from the dictionary will be added to your SQL query.

	Dictionary files must be based on an ODBC data source.
  Hands-On Tutorial Topics

  FROM clause


How to link tables and specify a join type

WHERE clause

The WHERE clause specifies how two database tables are linked.

	Some links can be generated in the FROM clause.
1	Click the Links Tab and click the Smart Linking button. The Create SQL Expert will make any links it can detect between tables. These links are represented by an arrow between the two fields. This arrow is called a link line.

2	Click one of the link lines. It becomes highlighted, along with the fields on either end of the line.

3	Click the Options button to open the Link Options dialog box. This dialog box describes the link between the tables in greater detail.

4	Select an SQL join type.

  Hands-On Tutorial Topics

  SQL join types

  WHERE clause


How to add field(s)

SELECT clause

The SELECT clause selects specific data items to retrieve from the database tables specified by the FROM clause.

1	Click the Fields Tab in the Create SQL Expert.

2	Highlight the field on the Database Fields list you want to appear in your query.

3	Click the Add button to add it to those displayed in the Query Fields list box.

4	Click Remove to remove any fields from the Query Fields list box.

  Hands-On Tutorial Topics

  SELECT clause


How to identify unique values

DISTINCT clause

DISTINCT forces the query to retrieve only unique (distinct) sets of data.

1	Click the Fields Tab of the Create SQL Expert.

2	Toggle the Select Distinct Values check box on to activate it.

  Hands-On Tutorial Topics

  DISTINCT clause


How to summarize data with aggregate functions

GROUP BY clause

The GROUP BY clause retrieves a set of summary data.

Aggregate functions

Use aggregate functions to obtain summary information on all records or on specific groups of records. Aggregate functions are most useful when you do not need the detailed information and you only want to examine totals.

For example, you might need the total number of orders made and the average amount of each order. For this type of query, you apply the Count function to the Order ID field, and the AVG function to the Amount field. The query calculates the summary information and provides the results you need.

1	Click the Fields Tab of the Create SQL Expert.

2	Highlight the field you want to summarize from the Query Fields list.

3	From the Total drop-down list, choose an aggregate function to apply to the selected field.

•	COUNT( ) counts the number of values within a group.

•	SUM( ) adds the values within a group, to provide a total.

•	AVG( ) finds the average of all values within a group.

•	MIN( ) finds the minimum value within a group.

•	MAX( ) finds the maximum value within a group.

The query summarizes the field to which the aggregate function is applied. Any other fields that appear in the Query Fields list box are used to sort the data.

  Hands-On Tutorial Topics

  GROUP BY clause


How to sort records according to field values

ORDER BY clause

The ORDER BY clause specifies that the database records retrieved be sorted according to the values in a specific field.

You can group either by sorting data, so that records with like data appear grouped in the sorted list, or by summarizing data with aggregate functions, so that summary data appears in your query for each group of records.

1	Click the Sort Tab in the Create SQL Expert.

2	Highlight the field you want sorted from the Database Fields list.

3	Click the Add button. The field is added to the Group Fields list.

4	Select the order from the Order drop-down list in which you want the data sorted.

  Hands-On Tutorial Topics

  ORDER BY clause


How to specify records to be included

WHERE clause

The WHERE clause can specify record selection criteria.

1	Click the Select Tab of the Create SQL Expert.

2	Highlight the field that contains the data you want to base selection criteria on from the Database Fields list.

3	Click the Add button. The field is added to the Select Fields list.

4	Use the record selection drop-down lists to specify the selection criteria to be applied to the field highlighted in the Select Fields list box (Select Tab).

This tab works much like the Select Expert in Crystal Reports.

5	Repeat Steps 2 and 3 for every field in the Select Fields list box.

  Hands-On Tutorial Topics

  WHERE clause


How to select groups to be included

GROUP BY and HAVING clauses

The HAVING clause creates selection criteria for the summary information produced by the GROUP BY clause.

1	Click the Fields Tab of the Create SQL Expert.

2	Highlight the field from the Database Fields you want to summarize.

3	Select the aggregate function from the Total drop-down list that you want to apply to the highlighted field.

4	Click the Select Tab.

5	Select the aggregate function from the Database Fields list box.

6	Click the Add button. The field is added to the Select Fields list.

7	Use the selection criteria drop-down lists to specify which group summary values based on the aggregate function should appear in the result.

  Hands-On Tutorial Topics

  GROUP BY clause

  HAVING clause


How to create an SQL expression

	You must have some familiarity with the SQL language and SQL expressions before trying to add an expression to your SQL Designer file.
1	Click the Fields Tab in the Create SQL Expert.

.

2	Click the Expression button. The SQL Expression Name dialog box appears.

3	Type a name for the new expression and then click OK. The SQL Expression dialog box appears.

4	Double-click a field in the Fields list to add it to the expression.

5	Edit the expression in the SQL edit box.

6	When you have finished designing the SQL expression, click OK to return to the Fields Tab.

7	Highlight the new SQL expression. It will be indicated by an @ sign.

8	Click the Add button. The field is added to the Query Fields list.

  Hands-On Tutorial Topics


How to create a query from another Crystal Query

1	Click the New button in the Crystal SQL Designer.

The New Query dialog box appears.

2	Click the Start from existing Crystal Query button.

3	Use the File Open dialog box to highlight the query (*.QRY) file that you want used to base a new query on, and click OK. The program runs the existing SQL query and displays the data set.

4	Click the Edit button.

The Create SQL Expert appears with the specifications for the selected query already in place.

5	Use the Create SQL Expert to make any changes necessary to the query. Click Preview Query when finished to view the new query results.

6	Choose Save As command from the File Menu to save the new query under a different name. The new query is saved in a separate file; your source query remains unchanged.

  Hands-On Tutorial Topics


How to select a query for a report

1	In Crystal Reports, click the New button on the standard toolbar.

The Report Gallery appears.

2	Click a Report Expert from the Report Gallery. The Create Report Expert appears.

3	On the Tables Tab, click Query. The File Open dialog box appears.

4	Use the controls in this dialog box to locate and highlight the query (.QRY) file you want to use to create a new report.

5	Click OK. The query file for your report is opened. Use the tools in the Create Report Expert to design a new report. Your query fields will appear in each section of the Expert just as any other database fields would. In the list boxes, the name of the query file will appear above the fields, where you would normally see the name of a database table.

	More than one query file can not be used in a report.
  Hands-On Tutorial Topics


How to create a parameter field for use in a query

A parameter field can be created while previewing a query in the SQL Designer.

1	Choose the Parameter Field command from the Edit menu. The Parameter Field dialog box appears.

2	Enter the following information in the fields provided:

•	Parameter Name: Enter the name you want to use to identify the parameter field.

•	Prompting Text: Enter the text you want to appear when the program prompts you.

•	Value Type: Enter the data type of the parameter field.

•	Default Prompting Value: Enter the value you want the program to use if you do not supply a new value.

•	Browse Field: Use this drop-down box to specify a default field.

3	Click OK. The parameter field can now be used in your query.

  Hands-On Tutorial Topics


How to use a parameter field in a query

After you have created a parameter field in the SQL Designer, return to the Create SQL Expert by choosing the Query command on the Edit menu.

1	Click the Select Tab to activate it.

2	Select the database field you would like to select records for from the Database Fields list box.

3	Click the Add button. The field now appears in the Select Fields list box and two drop-down boxes appear.

4	When you select equal to, one of, less than, greater than, between, starting with, or like from the second drop-down box, one or more additional drop-down boxes appears. The parameter field you created will appear in the field drop-down box.

5	Select the parameter field from the field drop-down box.

6	Click the SQL Tab to activate it. You will see exactly how the parameter field is used in the SQL statement.

  Hands-On Tutorial Topics


Crystal SQL Designer Commands Index

  Click the command of interest from the following list for further information:

File menu

New

Open

Save

Save As

Save Data with Query

Close

Log On Server

Log Off Server

Options

Exit

Edit menu

Query

Query Title

Parameter Field

Refresh Data

Verify Query

Window Menu

Tile Vertically

Tile Horizontally

Cascade

Arrange Icons

Close All

Help Menu

Contents

Search

About Crystal Reports

  The Crystal SQL Designer Help Main Index


COMMANDS


New command (File menu)

Use the New command to create a new SQL query. When you choose the New command, you can:

•	create a new query, or

•	use an SQL query that you designed elsewhere.

	The New button on the button bar has the same functionality as the New command on the File menu.
  Menu Commands Index


Open command (File menu)

Use the Open command to open an existing Crystal Query (*.QRY) file in the Crystal Query Designer.

•	If the query was designed using the Create SQL Expert, this command will open the Create SQL Expert with all of the information from the query file available displayed on the appropriate tabs.

•	If the query was entered directly into the SQL edit box, or if changes were made to the query on the SQL Tab of the Create SQL Expert, the Create SQL Expert will appear with only an SQL Tab. You can only make changes by directly editing the SQL statement.

	The Open button on the button bar has the same functionality as the Open command on the File menu.
  Menu Commands Index


Save command (File menu)

Use the Save command to save your query as a Crystal Query (*.QRY) file. Crystal Query files can be used to analyze database data or they can be used as data sets for designing and building reports using the Crystal Reports Designer.

	The Save button on the button bar has the same functionality as the Save command on the File menu.
  Menu Commands Index

  Save As command (File menu)


Save As command (File menu)

Use the Save As command if you need to save your query under a new name. When you choose the Save As command, you will be prompted to specify a new location and file name for the query. Any previously saved version of the query file remains unchanged. Any changes you have made appear only in the new version of the file.

  Menu Commands Index

  Save command (File menu)


Save Data with Query command (File menu)

Once your SQL query has retrieved the necessary data and generated your query results, use the Save Data with Query command to save the data your query retrieved with the query file. This command is only a flag. A check mark appears next to the command if the option is turned On. Simply choose the command to toggle this option On and Off.

When data is saved with your query file, the query can be viewed over and over again without waiting through the time consuming process of accessing the appropriate database tables, querying the tables, and retrieving the data. Time and network traffic are reduced. If you ever need to update the query with fresh data from the database, use the Refresh Data command on the Edit menu.

  Menu Commands Index


Close command (File menu)

Use the Close command to close the currently active query file. Crystal Query Designer can have more than one query file open at the same time. To close a query file without affecting any other open files:

1	Select the file you want to close by clicking on its title bar in the Crystal Query Designer window or by selecting it from the list under the Window menu. The appropriate window becomes active.

2	Choose the Close command from the File menu. The query file is closed.

If you have not saved the file before selecting Close, you will be prompted to save the file before closing it.

  Menu Commands Index


Log On Server command (File menu)

Use the Log On Server command to log onto a data source. For example, if you expect to be working in the Crystal Query Designer with one or more SQL or ODBC data sources for a longer period of time, you may find it easier to log on to the data source(s) separately to avoid logging in over and over again from the Create SQL Expert.

When you choose this command, the Log On Server dialog box appears with a list of all the data sources you can log on to. You will remain logged on until you select the Log Off Server command or until you exit the Crystal Query Designer.

  Menu Commands Index


Log Off Server command (File menu)

Use the Log Off Server command if you have logged on to an SQL or ODBC data source with the Log On Server command (File menu), and you no longer need to access that data source.

When you choose this command, the Log Off Server dialog box appears with a list of all data sources you are currently logged on to.

1	Highlight the data source you want to log off of, and click Log Off.

2	Click Done when finished.

Staying logged on to data sources that you no longer need increases network traffic and ties up valuable system resources. You should make a point of logging off any data sources you do not need.

  Menu Commands Index


Options command (File menu)

Use the Options command to open the Options dialog box. The Options dialog box allows you to change default Crystal Query Designer options such as the default location for Query files, Crystal Query Designer display settings, and default SQL options.

  Menu Commands Index


Exit command (File menu)

Use the Exit command to exit the Crystal Query Designer application. If there are any unsaved query files open in the Query Designer window, you will be prompted to save them before exiting the application.

  Menu Commands Index


Query command (Edit menu)

Use the Query command to edit a query that is open in the Crystal Query Designer window.

If the query was designed using Create SQL Expert, the Expert will open with all query information displayed.

If the query was entered directly into the SQL edit box, or if changes were made to the query on the SQL Tab of the Create SQL Expert, the Create SQL Expert will appear with only an SQL Tab. You can only make changes by directly editing the SQL statement.

	The Edit button on the button bar has the same functionality as the Query command on the Edit menu.
  Menu Commands Index


Query Title command (Edit menu)

Use the Query Title command to change the title saved with the query file. Every Crystal Query you create can have a title describing the purpose of the query. This title is saved with the query (*.QRY) file and is retrieved by various applications to help identify the query.

  Menu Commands Index


Parameter Field command (Edit Menu)

Use the Parameter Field command to create a parameter field for use in your query.

  Menu Commands Index


Refresh Data command (Edit menu)

If you believe database data has changed since you last generated your SQL query, use the Refresh Data command to retrieve fresh data from the database and regenerate the query results. This is especially useful if, for example, you open a Crystal Query file that has data saved with it, and you need to update that data to reflect the most recent changes to the database.

	The Refresh button on the button bar has the same functionality as the Refresh Data command on the Edit menu.
  Menu Commands Index

  Save Data with Query command (File menu)


Verify Query command (Edit menu)

Use the Verify Query command to make certain that your query is accessing the most current version of the active database. When you choose this command, a message box appears verifying the status of the database currently in use and the actions to be taken.

  Menu Commands Index


Tile Vertically command (Window menu)

Use the Tile Vertically command to display your Crystal Query Designer windows side by side on screen. With this command, windows may be resized so they all fit on screen; all windows are visible.

  Menu Commands Index


Tile Horizontally command (Window menu)

Use the Tile Horizontally command to display your Crystal Query Designer windows one above the other on screen. With this command, windows may be resized so they all fit on screen; all windows are visible.

  Menu Commands Index


Cascade command (Window menu)

Use the Cascade command to stack and overlap your Crystal Query Designer windows. With this command, the entire top window is visible but only the title bars of the remaining windows are visible.

  Menu Commands Index


Arrange Icons command (Window Menu)

If you have minimized any document windows, use the Arrange Icons command to arrange the document icons neatly at the bottom of the Crystal Query Designer window.

  Menu Commands Index


Close All command (Window Menu)

Use the Close All command to close all open windows. The Crystal Query Designer gives you an opportunity to save any work in each window that you have not already saved. When you choose this command:

•	If you have saved all your queries, the Crystal Query Designer closes all the windows.

•	If you have not saved all your queries, the Crystal Query Designer prompts you to save them, query by query:

—	Click Yes to save the query before closing the window.

—	Click No to close the window without saving.

  Menu Commands Index


Contents command (Help menu)

Use the Contents command to call up the Crystal Query Designer Online Help Index. Using this index as a starting point, you can quickly find any Help topic of interest. Once in online Help, you can always return to the main window by clicking the Contents button in the upper left corner of the Help window.

  Menu Commands Index


Search command (Help menu)

The Search command displays the Search dialog box for the Crystal Query Designer online Help. You can search through every subject in online Help to find the information you need quickly. Jump straight to the topic of interest from this dialog box.

  Menu Commands Index


About Crystal Reports command (Help menu)

Use the About Crystal Reports command to find the version number and other pertinent information about Crystal Reports.

  Menu Commands Index


Dialog Box Index

  Click the dialog box of interest from the following list for further information:

Create SQL Expert

Browse Data dialog box

Browse and Paste Data dialog box

Choose SQL Table dialog box

Choose Tables to Use in Visual Linking dialog box

Enter SQL Statement dialog box

Link Options dialog box

Log Off Server dialog box

Log On Server dialog box

New dialog box

New Query dialog box

Options dialog box

Query Title dialog box

SQL Expression Name dialog box

SQL Expression dialog box

SQL Options dialog box

Table Description dialog box


Create SQL Expert

The Create SQL Expert consists of several tabs. The tabs are numbered to lead you step-by-step through the query creation process.

  Click the area of interest on the picture below for further information:

	The Links Tab will only appear if you have selected two or more tables from the Tables Tab.
  The Crystal SQL Designer Help Main Index


Tables Tab

The Tables Tab lets you select the data sources you want to query. You can query data from:

•	a previously set up Dictionary (your Dictionary must contain SQL or ODBC data), or

•	SQL or ODBC data sources.

	Since the Create SQL Expert uses the SQL language, it can only be used with databases that recognize this language. For this reason, you can not use the Create SQL Expert to open a database other than an ODBC data source, either directly, or through a Dictionary.
—	If you click the Dictionary button, a File Open dialog box appears for you to select a Crystal Dictionary (*.DC5) file. Select the Dictionary you wish to use. All table aliases within the Dictionary will be added to your SQL query.

—	If you click the SQL/ODBC button, the Log On Server dialog box appears for you to log on to an SQL or ODBC data source. Once you log on, the Choose SQL Table dialog box opens. Use this dialog box to specify the database table(s) you want to use in your query. Select the tables one by one in the SQL Tables list box and click the Add button after you make each selection. Click the Done button when finished. As you add tables to your SQL query, the program adds the tables to the Tables list box on the Tables Tab.

	The Create SQL Expert will not let you continue on to any other step in the query creation process until you have selected at least one table from an ODBC data source or from a Dictionary.
  Click the area of interest on the picture below for further information:

  Create SQL Expert


Query Name edit box (Tables Tab)

Enter the query file a name or title that describes the purpose of the query into this edit box. This title is used by other applications to help you identify the query file. You can also edit the Query Title after the query has been generated by using the Query Title command on the Edit menu.


Tables list box (Tables Tab)

This list box displays all the tables that you have selected for the query.

•	When you select new tables, the program adds them to the list.

•	When you delete tables using the Delete button, the program removes them from the list.


Dictionary button (Tables Tab)

Use this button if you wish to create a query using data stored in a Dictionary (*.DC5) file. The Dictionary must be based on SQL or ODBC database data.


SQL/ODBC button (Tables Tab)

Click this button to open the Log On Server dialog box. Using this dialog box, you can log onto an SQL/ODBC data source from which you can select tables for your query.


Delete button (Tables Tab)

Click this button to delete the table that is currently selected in the Tables list box from your query.


Links Tab

If you add more than one table to your SQL query, the program displays the Links Tab in the Create SQL Expert. Use this tab to specify the links between the tables. If you select only a single table, the Links Tab will not appear.

The Query Designer uses Smart Linking, a process that suggests links automatically. If you are satisfied with the suggested links, you have nothing more to do on this tab. If you want to change the links, add additional tables, etc., you will find all the tools you need.

	If you make changes to the links on the Links Tab, then decide that you want to return to the original settings, simply click the Smart Linking button. The Create SQL Expert will automatically reset the links.
  Click the area of interest on the picture below for further information:

  Create SQL Expert


Options button (Links Tab)

Click this button to open the Link Options dialog box, which you can use to:

•	fine tune the way the program performs lookups on linked databases, or

•	specify the kind of join made between tables.

The button becomes active whenever you select the line between linked fields.

	To select a link line, move the cursor over the line and click once. The line and the fields it links will change color when the line is selected.


Smart Linking button (Links Tab)

Click this button to have the Create SQL Expert perform Smart Linking on your selected databases. Smart Linking identifies the link fields automatically if possible and creates a link line between linked databases in the dialog box.


Delete button (Links Tab)

Click this button to delete an existing link on the Links Tab. To delete a link, position the tip of the mouse pointer on the line that represents the link you want to delete and click once to select the link. When the link is selected, the line and the names of the linked fields change color. Click the Delete button to delete the link.


Arrange button (Links Tab)

Click this button to arrange the tables on the Links Tab. This feature is only a convenience that makes viewing the tables and understanding the links easier.


Tables button (Links Tab)

Click this button to open the Choose Tables to Use in Visual Linking dialog box. Use this dialog box to select which Tables will be visible in the Links Tab and which will be invisible. This dialog box can also provide information on the SQL or ODBC tables that appear in your query, and allows you to decide if Smart Linking should be automatic.


Fields Tab

Once you have selected your tables (and, if required, created your links), you need to specify the fields you want to include in your query. You do this on the Fields Tab.

The Database Fields list box on the left lists the tables that you have added to your query, along with the fields that appear in each table. The Query Fields list box on the right shows which fields you have selected to appear in the query. To select a field for your query, highlight the field in the Database Fields list, and click the Add button (or double-click the desired field). The field will appear in the Query Fields list on the right.

	When you select a field here, you are adding code to the SQL statement that retrieves all the data in that field and returns it to your system. You can also control how much of the data in a field is returned by using the Select Tab.
In addition to selecting database fields, you can also summarize data using the Fields Tab. To summarize data, you simply highlight the field you want to summarize (from the Query Fields list box), and select the kind of summary you want (aggregate/column function) from the Total drop-down box. If you want to summarize only distinct values, toggle the Distinct Values check box On, as well. The program flags the summarized field in the Query Fields list by adding the summary type to the field name.

If you do not specify any other grouping/sorting options using the Sort Tab, the program automatically sorts and groups by the remaining query fields in the order they appear in the Query Fields list. For example, if you have three query fields (State, Zipcode, and Amount, appearing in that order) and you sum the Amount field, the program automatically groups by the State field first and then by the Zip code field.

	You have to select at least one table and one field for a query. When you select the field, the Preview Query button becomes active, enabling you to send the query to the server.
  Click the area of interest on the picture below for further information:

  Create SQL Expert


Database Fields list box (Fields Tab)

This list box displays all the fields from the database(s) that you selected on the Tables Tab. Select the fields you want to include in your query from this list. You may also choose any SQL Expressions you have entered, and any fields that have already been added to query.

	If this query has already been run once, any fields or expressions that appeared in it will also be available in this list box.


Query Fields list box (Fields Tab)

This list box displays all the fields you have selected for your query. You add fields to this list box using the Add -> and All->> buttons. You remove fields from this list box using the <-Remove and <<-All buttons.


Up/Down Arrow buttons (Fields Tab)

When you run a query, the program generates an SQL query statement that SELECTS fields in the same order as they appear in the Query Fields list box. If you need to change the order of the fields in the SELECT statement, you can do this by using the Up/Down Arrow buttons to change the order in which the fields appear in the Query Fields list.

The order in which the fields appear initially is the order that the fields were added to the Query Fields list box.

	You can also modify the query statement directly working from the SQL Tab.


Add-> button (Fields Tab)

Click this button to add the currently highlighted field in the Database Fields list box to your query. When you click this button, the field will appear in the Query Fields list box.

	If you want to include all the fields in the Database Fields list box in your query, click the All->> button. The program will add all the fields to the list in the Query Fields list box.


All->> button (Fields Tab)

Click this button if you want to add all the fields from the Database Fields list box in your query. When you click this button, all of the currently available fields will be appear in the Query Fields list box.


<-Remove button (Fields Tab)

Click this button to remove the currently selected field in the Query Fields list box from your query.


<<-All button (Fields Tab)

Click this button if you want to remove all the fields listed in the Query Fields list box from your query.


Browse button (Fields Tab)

Click this button to open the Browse Data dialog box, which displays the values contained in the selected field. The length of the field (if specified) and the data type of the field are also displayed at the top of the dialog box.

	This button is active only when you have a field selected in the Database Fields list box on the Fields Tab.


Total list box (Fields Tab)

This list box is used to create an Aggregate (Summary) Function. These are used to calculate a value based on a group of rows in the query.


Expression button (Fields Tab)

Click this button to create a new SQL Expression. When you click this button, two dialog boxes will appear. The first, allows you to type in the name of an expression and the second allows you to create the SQL expression. SQL Expressions are a way to take advantage of some of the powerful operations available in SQL.

  SQL Expressions


Select Distinct Values check box (Fields Tab)

Use this check box to prevent duplicate fields from appearing in the query more than once. This check box is particularly useful in conjunction with an Aggregate Function.

For example, if the COUNT function is used in your query, SQL will normally count every value that appears in the field that COUNT has been applied to. However, if you toggle the Select Distinct Values check box On, along with the COUNT function, SQL will count only distinct values in the field. Any duplicate values that appear within the field will be ignored. This SQL does not need to contain an aggregate function for this check box to work.


Preview Query button

Click this button when you have finished designing or editing your SQL query and want to view the query results. When you click this button, the Query Designer will send the SQL statement generated by the Create SQL Expert to the SQL or ODBC data source and retrieve the relevant data. The Create SQL Expert will disappear, and the query results will appear in the Design Window.


Sort Tab

If you want to sort your data, you will find that the Create SQL Expert makes sorting a simple process.

The list box on the left, Database Fields, contains the fields that you included in your query (from the Fields Tab). The list box on the right, Group Fields, displays the fields that will be used to sort your data when it is retrieved from the server. To add a field to the Group Fields list, simply select the field in the Database Fields list and click Add. The field then appears in the Group Fields list.

	Sorting takes place in the order fields appear in the Group Fields list (if Region is first on the list followed by City, the server sorts the data first by region and then, within each region, by city).
  Click the area of interest on the picture below for further information:

  Create SQL Expert


Database Fields list box (Sort Tab)

This list box displays all the fields from the database(s) that you selected on the Tables Tab (not just fields you selected on the Fields Tab), along with any SQL expressions you entered and any fields that have already been added to the query. Select the field(s) from this list that you want your query sorted and grouped by.


Group Fields list box (Sort Tab)

This list box displays the fields that you add from the Database Fields list box. The fields contained in this list box are the fields that the query will be sorted and grouped by.


Add-> button (Sort Tab)

Click this button to add the currently selected field in the Database Fields list box to the Group Fields list box. Your query will base the sort order on those fields in the Group Fields list box.


<-Remove button (Sort Tab)

Click this button to remove the highlighted field from the Group Fields list box.


Browse Data button (Sort Tab)

Click this button to open the Browse Data dialog box, which displays the values contained in the selected field. The length of the field (if specified) and the data type of the field are also displayed at the top of the dialog box.

	This button is active only when you have a field selected in the Database Fields list box on the Sort Tab.


Order list box (Sort Tab)

This list box allows you to choose the sort order of the selected field in the Group Fields list box. The three choices are:

1.	Ascending
Lowest to highest (1 to 9, A to Z, etc.)

2.	Descending
Highest to lowest (9 to 1, Z to A, etc.)

3.	Original Order
Order that the records exist in the data source.


Up and Down arrows (Sort Tab)

If more than one field appears in the Group Fields list box, SQL will sort the fields in the order that they appear in the list box. In other words, all records are first sorted by the field that appears first in the Group Fields list box, then, within that sort, records will be sorted by the field that appears second, etc.

If you wish to change the order of the fields in the Group Fields list box to alter the sorting priority, select a field and click the Up or Down arrow to change its position within the list box. Alternatively, you can also drag and drop a field into position.


Select Tab

There may be times when you do not want your query to include field values from all the records in your database. For example, a sales manager whose region covers the state of California may want the query to pull in all data from California but nothing from any other state. You can easily limit the records included in the query using the Select Tab.

The list box on the left, Database Fields, displays all of the fields available in all of the tables you have added to your query. Notice that you are not restricted to just the fields you have selected to appear in the query. It is possible to generate a query based on a selection of records from a field that does not appear in the query. The list box on the right, Select Fields, displays all of the fields on which record selection is based.

From the Database Fields list, you select the field(s) on which you want to base your record selection and click the Add button. The field(s) you select are added to the Select Fields list.

To set up record selection, you first pick the field and then you pick the record selection criteria for that field. For example, if you want to limit your query to customers that have the value California (or CA) in the State field, you select the State field and click Add. When you do this, the State field appears in the Select Fields list and the program displays two drop-down lists. Use these lists to fine tune your record selection.

  Click the area of interest on the picture below for further information:

  Create SQL Expert

  How to specify records to be included

  How to select groups to be included


Database Fields list box (Select Tab)

This list box displays the fields from the database(s) that you selected on the Tables Tab (not just fields added to the query on the Fields Tab). Records can be selected based on field values that do not even appear in the query, depending on your needs.


Select Fields list box (Select Tab)

This list box displays fields that you have added from the Database Fields list box. The fields contained within this box are the fields for which you have specified or will specify selection criteria on. Each field has its own criteria.


Add-> button (Select Tab)

Click this button to add the currently selected field in the Database Fields list box to the Select Fields list box. The fields displayed in the Select Fields list box are those for which you will specify selection criteria.


<-Remove button (Select Tab)

Click this button to remove the currently selected field from the Select Fields list box.


Browse Data button (Select Tab)

Click this button to open the Browse Data dialog box, which displays the values contained in the selected field. The type and length of the field (if it is a string) are also displayed at the top of the dialog box.

	This button only works when you have a field selected in the Database Fields list box. Use the Paste button in this dialog box to paste a value into your selection criteria.


Record Selection options (Select Tab)

The Record Selection options present different drop-down list boxes, edit boxes, and check boxes depending on your selections. The goal is to complete a sentence with these options that begins "Select only those records in which the value of the selected field is."

  Record Selection Options


SQL Tab

The SQL Tab in the Create SQL Expert enables you to examine and, if you wish, to modify the SQL statement the program generates based on your query specifications.

If you are not familiar with the SQL language, you should skip this tab and simply click the Preview Query button when finished with the Create SQL Expert to view the results of your query. If you know SQL, however, you can use the SQL Tab to fine tune your SQL query statement. If you want to use advanced SQL features that are specific to the SQL server you are using, simply add the SQL code into the statement as you normally would. This SQL statement is fully editable and lets you access the full power of your SQL database server.

  Click the area of interest on the picture below for further information:

  Create SQL Expert

  SELECT Clause

  FROM Clause

  WHERE Clause

  GROUP BY Clause

  ORDER BY Clause

  HAVING Clause


Query edit box (SQL Tab)

This is the edit box you can use for editing the SQL statement that is sent to retrieve your data. Place the cursor where you want to make changes and modify the statement to fit your needs.


Cut button (SQL Tab)

Use this button to cut the currently selected text from the SQL statements and save it to the clipboard.


Copy button (SQL Tab)

Use this button to copy the currently selected text from the SQL statement to the clipboard.


Paste button (SQL Tab)

Use this button to paste the text currently residing on the Clipboard to the current cursor position.


DIALOG BOXES


Browse Data dialog box

This dialog box appears when:

•	you click the Browse button on the Fields Tab,

•	you click the Browse Data button on the Sort Tab, or

•	you click the Browse Field Data button on the Select Tab.

Use the Browse Data dialog box to preview the data type, length, and/or content of any field.

•	The source of the field (alias) and the field name appear as the title for the dialog box.

•	The data type for the field (string, number, etc.) appears just below the title.

•	A list of field values appears in the scroll box.

When you are finished reviewing the field data, click the Done button.

  Dialog Box Index


Browse and Paste Data dialog box

This dialog box appears when you click the Browse button on the Table Description dialog box.

Use the Browse and Paste Data dialog box to preview the data type, length, and/or content of any field and to paste a value into your formula or selection criteria.

•	The source of the field (alias) and the field name appear as the title for the dialog box.

•	The data type for the field (string, number, etc.) appears just below the title.

•	A list of field values appears in the scroll box.

When you are finished reviewing the field data, click the Done button or highlight a value and click the Paste Data button to paste the value into your formula.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Field value list box (Browse and Paste Data dialog box)

This box displays a list of all the values for the currently selected field.


Paste Data button (Browse and Paste Data dialog box)

Click this button to paste the currently highlighted value in the Field value list box.


Choose SQL Table dialog box

Once you have logged onto an SQL/ODBC data source, this dialog box appears when:

•	you click the SQL Table button in the Choose Database File dialog box,

•	you click the Add SQL/ODBC button in the Choose Tables To Use In Visual Linking dialog box, or

•	you first choose an SQL/ODBC data source in the Log On Server dialog box and click OK.

Use the Choose SQL Table dialog box to select the SQL/ODBC table you want to activate for use in your query.

  Click the area of interest on the picture below for further information:

  Dialog Box Index

  Log On Server dialog box


SQL Tables list box (Choose SQL Table dialog box)

This box lists all of the tables in the active database. Select the table you want to include in your query from this list.


SQL Databases list box (Choose SQL Table dialog box)

This box lists all of the databases you have activated. Select the database that contains the table you want to use in your query from this list.


Server Info box (Choose SQL Table dialog box)

This box identifies the active server, the active data source, and the user who activated them.

Server Type	The type of data source, ODBC, for example.
Server Name	The name of the active data source.
Database	The name and path of the data file specified in the data source.
User ID	The user who logged onto the active data source


Log On Server button (Choose SQL Table dialog box)

This button returns you to the Log On Server dialog box. Click this button when you want to activate another database. When you return to the Log On Server dialog box, highlight the server type and click OK, and then enter the name of the database you want to activate and the other requested log in information in the Server Login dialog box when it appears.


Database File button (Choose SQL Table dialog box)

This button takes you to the Choose Database File dialog box. Once in that dialog box you can activate non-SQL databases for use in your query.


Choose Tables to Use in Visual Linking dialog box

This dialog box appears when you click the Tables button on the Links Tab.

Use the Choose Tables to Use in Visual Linking dialog box to choose the tables you want to link in your query.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Linked Tables list box (Choose Tables to use in Visual Linking dialog box)

This list box displays all those tables that are currently linked.


Visible Tables list box (Choose Tables to use in Visual Linking dialog box)

Visible tables are the tables you want to use in your query. When you add a new data file to your query by clicking the Add Data File button, the program lists all the tables in that data file in the Visible Tables box unless there are more tables than you might practically use in a query. In that case the tables are listed in the Invisible Tables box and you can select the tables from that box that you want to make visible.

•	When you link a table from the Visible Tables box, the program moves the table name from the Visible Tables box to the Linked Tables box.


Invisible Tables list box (Choose Tables to use in Visual Linking dialog box)

Invisible tables are the tables you do not want to use in your query.

•	To make an invisible table visible, select the table in the Invisible Box and click the Add button with the left arrow. This adds the tables to the Visible Box. You can also click the Add All button with the left arrow to add all the tables to the Visible section.

•	To make a visible table invisible, select the table in the Visible Box and click the Add button with the right arrow. This adds the tables to the Invisible Box. You can also click the Add All button with the right arrow to add all the tables to the Invisible section.


Add Data File button (Choose Tables to use in Visual Linking dialog box)

Use the Add Data File button to add another database to your query. When you click the Add Data file button, the Choose File dialog box appears. Use this dialog box to highlight the database file or files you want to add to your query. Each time you highlight a file and click the Add button, the database file is added to the Visible Tables box unless there are more tables in the data file to practically use in a query. In this case the tables are added to the Invisible Tables box.

When you have added the database or databases you need, click Done to return to the Choose Tables To Use In Visual Linking dialog box. The database or databases you added will be in the Visible Tables box.


Add SQL/ODBC button (Choose Tables to use in Visual Linking dialog box)

The Add SQL/ODBC button lets you add a database from an SQL or ODBC source. When you click the Add SQL/ODBC button, the Log On Server dialog box appears. Use this dialog box to log onto an SQL/ODBC server and select a database for use in your query.


Perform Smart Linking check box (Choose Tables to use in Visual Linking dialog box)

The Perform Smart Linking check box is a toggle. When the check mark is visible, Smart Linking is activated. When the check mark is not visible, Smart Linking is inactive.

When Smart Linking is turned on, and you click OK in the Choose Tables To Use In Visual Linking dialog box, Smart Linking identifies suggested link fields automatically and performs the links.


Add Index button (Choose Tables To Use In Visual Linking dialog box)

Click this button to add an index to the currently selected table.


Table Description button (Choose Tables To Use In Visual Linking dialog box)

When you click this button, the Table Description dialog box appears giving you a description of the currently selected table.


All >> button (Choose Tables To Use In Visual Linking dialog box)

Click this button to add all the tables displayed in the Visible Tables list box to the Invisible Table list box.


Add > button (Choose Tables To Use In Visual Linking dialog box)

Click this button to add the selected table in the Visible Tables list box to the Invisible Table list box.


< Add button (Choose Tables To Use In Visual Linking dialog box)

Click this button to add the selected table in the Invisible Tables list box to the Visible Table list box.


<< All button (Choose Tables To Use In Visual Linking dialog box)

Click this button to add all the selected tables in the Invisible Tables list box to the Visible Table list box.


Enter SQL Statement dialog box

This dialog box appears when you click the Enter SQL Statement directly button in the New Query dialog box.

Use the Enter SQL Statement dialog box to enter your SQL statement directly.

The query can have up to four parts:

SELECT

SELECT lists all of the fields used in the query, including fields used in formulas, totals, the sort order, the selection formula, and ranges. For example:

SELECT
DIV_INFO.DIVISION_ID, DIV_INFO.DIVISION_NAME,
DEP_INFO.DEPARTMENT_ID, DEP_INFO.DEPARTMENT_NAME

«Shows that the query is using the DIVISION_ID, DIVISION_NAME, DEPARTMENT_ID, and DEPARTMENT_NAME fields.»

FROM

FROM lists all the tables used in the query, and each table name is followed by an alias name. For example:

FROM
SYSADM.DIV_INFO DIV_INFO,
SYSADM.DEP_INFO DEP_INFO

«Shows that the query is using the DIV_INFO and DEP_INFO tables from the SYSADM database, and in each case, the alias name is the same as the table name.»

WHERE

WHERE specifies any links between tables and field ranges for the query. For example:

WHERE
DIV_INFO.DIVISION_ID = DEP_INFO.DIVISION_ID

«Shows that the DIV_INFO and DEP_INFO tables are linked via the DIVISION_ID fields.»

ORDER BY

ORDER BY sorts the data in alphabetic or numeric ascending or descending order. For example:

ORDER BY
DIV_INFO.DIVISION_ID ASC

«Sorts the records in ascending order by the values that appear in the DIV_INFO.DIVISION_ID field.»

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Data Source drop down box (Enter SQL Statement dialog box)
Data Source drop-down box (Enter SQL Statement dialog box)

Select the data source for your SQL query from this drop-down box.


SQL edit box (Enter SQL Statement dialog box)

Enter your SQL statement directly into this edit box. You can use the cut, paste and copy buttons as editing tools.


Import button (Enter SQL Statement dialog box)

Click this button to import a text file (*.TXT) including an already existing SQL statement to use in your query.


Cut button (Enter SQL Statement dialog box)

Click this button to cut text from the SQL edit box and place it in the Clipboard.


Paste button (Enter SQL Statement dialog box)

Click this button to paste the text from the Clipboard into the SQL edit box.


Copy button (Enter SQL Statement dialog box)

Click this button to copy the currently highlighted text in the SQL edit box to the Clipboard.


Log On Server button (Enter SQL Statement dialog box)

Click this button to log on to an SQL Server.


Title edit box (Enter SQL Statement dialog box)

Enter a title for your query into this edit box.


Link Options dialog box

This dialog box appears when you have a link line highlighted and you click the Options button in the Links Tab.

Use the Link Options dialog box to specify join types and the criteria the program is to use when linking a single table to two or more tables (A to B, A to C).

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Link description box (Link Options dialog box)

This box describes the highlighted link. The field(s) listed on the left are the link from field(s) and the field(s) listed on the right are the link to field(s).


Index In Use drop down box (Link Options dialog box)
Index In Use drop-down box (Link Options dialog box)

This drop-down box displays the index that is currently in use. If you click the arrow it also lists the other indexes that are available for the link as well as the option <no specific index>.


Fields In Index list box (Link Options dialog box)

This drop-down box lists the fields that are included in the index that is currently selected in the Index in Use drop-down box.


Allow partial text matches check box (Link Options dialog box)

This check box is for Paradox and dBASE users. Use it to specify whether the program should have partial text matching enabled when linking.

  Allow Partial Text Matches


Allow Partial Text Matches

When you create any link, the Link Options dialog box displays the Allow Partial Text Matches check box. This check box is for Paradox and dBASE users. Using it, you specify whether the program should have partial text matching enabled when linking. To understand this option, assume the following data:

File A	File B
John	Jonathan
Mary	John
Frank	Frank
Chris	Chris
Gregory	Christopher
	Greg
Partial linking On

If partial linking is On (a check in the check box) when you link File A to File B, the following records will report:

File A	File B
John	Jonathan
John	John
Mary
Frank	Frank
Chris	Chris
Chris	Christopher
Gregory
•	In this case, the program links if it finds an exact text match (John with John, Chris with Chris).

•	It also links if it finds a partial text match, a value in File B that begins with a value from File A. Thus John links with Jonathan because Jonathan begins with a value from File A, John.

•	Note that Gregory does not link with Greg because Greg (the File B value) does not begin with Gregory (the File A value). If Greg was in File A and Gregory was in File B, however, there would be a link.

Partial linking Off

If partial linking is Off when you link File A to File B, the following records will report:

File A	File B
John	John
Mary
Frank	Frank
Chris	Chris
Gregory
•	In this case, the program links only if it finds an exact match. John will link to John (an exact match) but John will not link to Jonathan (not an exact match).


When linking to two files from this file options (Link Options dialog box)

Three different options for looking up records are available:

1.	Look up both files at the same time,

2.	Look up all of one file, then all of the other, and

3.	Look up all combinations of the two files.

  When Linking To Two Files From This File


When Linking To Two Files From This File

The Link Options dialog box displays three different options for looking up records:

1.	Look up both files at the same time.

2.	Look up all of one file, then all of the other.

3.	Look up all combinations of the two files.

Examples of each of these options follows.

Sample Data

Examples of the three different options for looking up records will be based on the following data:

Database A - Customers

CustNum	Name
1	Jones
2	Smith
3	Miller
Database B - Orders

OrderNum	CustNum	Amt1
11	1	10.00
22	1	20.00
33	2	30.00
44	2	40.00
55	3	30.00
66	3	30.00
Database C - Credits

CreditNum	CustNum	Amt2
C1	1	10.00
C2	2	30.00
C3	2	40.00
C4	3	30.00
Lookup option 1 - Look up both files at the same time.

For each record in A, this option looks for a matching record in B and a matching record in C, then it looks for the next matching record in B and the next matching record in C, etc. Once it finds all the matching records, it repeats the process with the next record in A, then the next, etc.

Using the example databases, the Crystal Query Designer presents the data in this manner using this function:

CustNum	OrderNum	Amt1	CreditNum	Amt2
1	11	10.00	C1	10.00
1	22	20.00	C1	10.00
2	33	30.00	C2	30.00
2	44	40.00	C3	40.00
3	55	30.00	C4	30.00
3	66	30.00	C4	30.00
Lookup option 2 - Look up all of one file, then all of the other.

For each record in A, this option looks for all the matching records in B and then all the matching records in C, then it repeats the process with the next record in A, then the next, etc.

Using the example databases, the Crystal Query Designer presents the data in this manner using this function:

CustNum	OrderNum	Amt1	CreditNum	Amt2
1	11	10.00
1	22	20.00
1			C1	10.00
2	33	30.00
2	44	40.00
2			C2	30.00
2			C3	40.00
3	55			30.00
3	66			30.00
3			C4	30.00
	If you want the C data to appear in your query before the B data, you will need to change your links so the A to C link comes first, then the A to B link. You do this via the Link Options dialog box. To change the order of the links, delete the existing links and set up new links in the order you want.
Lookup option 3 - Look up all combinations of the two files.

For each record in A, this option looks for a matching record in B, then it finds all the matching records in C. Once it finds all the matching records in C, it repeats the process with the next record in B, then the next, etc. When it finds matching C records for all the B records that match the first A record, it moves to the next A record and repeats the process.

Using the example databases, the Crystal Query Designer presents the data in this manner using this function:

CustNum	OrderNum	Amt1	CreditNum	Amt2
1	11	10.00	C1	10.00
1	22	20.00	C1	10.00
2	33	30.00	C2	30.00
2	33	30.00	C3	40.00
2	44	40.00	C2	30.00
2	44	40.00	C3	40.00
3	55	30.00	C4	30.00
3	66	30.00	C4	30.00
	If you want the program to look up the first matching record in the C database, then find all matching records in the B database (the reverse of the current process), you will need to change your links so the A to C link comes first, then the A to B link. You do this via the Links Tab. To change the order of the links, delete the existing links and set up new links in the order you want.


SQL Join Type options (Link Options dialog box)

The Crystal Query Designer enables you to specify the type of join you want it to use when joining SQL tables.

  SQL Join Types


Log Off Server dialog box

This dialog box appears when you choose the Log Off Server command from the File menu.

Use the Log Off Server dialog box to highlight the server(s) from which you want to log off. The Opened SQL Servers box lists the SQL servers that are currently activated.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Logged On Servers list box (Log Off Server dialog box)

This list box displays all the SQL servers that are currently activated.


Server Info information box (Log Off Server dialog box)

This box displays identifying information for the server that is highlighted in the Logged On Servers list box.


Log Off button (Log Off Server dialog box)

Click this button to log off the server that is highlighted in the Logged On Servers list box.


Log On Server dialog box

This dialog box appears when:

•	you choose the Log On Server command from the File menu,

•	you click the SQL Table button in the Choose Database File dialog box, or

•	you click the Add SQL/ODBC button in the Choose Tables To Use In Visual Linking dialog box.

Use the Log On Server dialog box to highlight the type of SQL server you want to log on to.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Database File button (Log On Server dialog box)

If active, this button takes you to the Choose Database File dialog box where you can activate non-SQL databases for use in your query.


Server Type list box (Log On Server dialog box)

The Server Type box lists the various SQL server types that are available to your system.


New dialog box

This dialog box appears when you do not have a parameter field chosen and you choose the Parameter Field command from the Edit menu.

Use the New dialog box to assign a name to your parameter field.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Name edit box (New dialog box)

Enter the name you want to assign to your parameter field in this edit box.


New Query dialog box

This dialog box appears when:

•	you click the New button on the toolbar, or

•	you choose the New command from the File menu.

Use the New Query dialog box as a starting point for creating/editing a query.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Use Seagate Query Expert button (New Query dialog box)
Use SQL Expert button (New Query dialog box)

Click this button to begin creating your query using the Crystal SQL Expert. The Expert will guide you through step-by-step instructions to assist you in the query creation process.


Enter SQL statement directly button (New Query dialog box)

Click this button to create your new query by entering your SQL statement directly. When you click this button the Enter SQL Statement dialog box appears where you can begin creating your query.


Start from existing Seagate Query (Enter SQL Statement dialog box)

Click this button to create a new query using an existing query as a template. When you click this button a File Open dialog box appears where you can highlight the query you would like to use as a template.


Options dialog box

This dialog box appears when you choose the Options command from the File menu.

Use the Options dialog box to set Crystal Query Designer options such as query location and SQL options, as well as displaying the button bar and status bar in the main window.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Browse button (Options dialog box)

If you do not know the name and path of the file, click this button to search for it. When you click this button, a File Open dialog box appears where you can locate the desired file.


Query location edit box (Options dialog box)

Enter the complete path to the desired query file in this edit box. If you do not know the path, simply click the Browse button to search for it.


SQL Options button (Options dialog box)

Click this button to set the default SQL server type, server name, database, user ID, and other SQL server options.


Display Button Bar check box (Options dialog box)

Toggle this option On if you want the button bar (toolbar) to be visible on screen at all times in the Crystal Query Designer main window.


Display Status Bar check box (Options dialog box)

Toggle this option On if you want the status bar to be visible on screen at all times in the Crystal Query Designer main window.


Query Title dialog box

This dialog box appears when you choose the Query Title command from the Edit menu.

Use the Query Title dialog box to add/modify the title of the currently active query.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Title edit box (Query Title dialog box)

Enter/modify the title for the currently active query in this edit box.


Description edit box (Query Title dialog box)

Enter a description (if desired) for the currently active query in this edit box.


SQL Expression Name dialog box

This dialog box appears when you click the Expression button on the Fields Tab in the Create SQL Expert.

Use the SQL Expression Name dialog box to assign a name to your SQL expression.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Enter a name edit box (SQL Expression Name dialog box)

Enter the desired name for your SQL expression into this edit box.


SQL Expression dialog box

This dialog box appears when you have entered a name into the SQL Expression Name dialog box and click the OK button to begin creating your SQL expression.

Use the SQL Expression dialog box to create your SQL expression.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Fields scroll box (SQL Expression dialog box)

This scroll box displays a list of all the currently available fields in your query. To add a field to your SQL expression, double-click the field in this scroll box. The field will appear in the SQL edit box below.


SQL edit box (SQL Expression dialog box)

Enter your SQL expression directly into this edit box.


SQL Options dialog box

This dialog box appears when you click the SQL Options button in the Options dialog box.

Use the SQL Options dialog box to set the default SQL server type, server name, database, user ID, and other SQL server options.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Server Type drop down box (SQL Options dialog box)
Server Type drop-down box (SQL Options dialog box)

Select a server type from this drop-down list. Then, when the Log On Server dialog box appears, your selection will be highlighted. If you then want to log on to that server, all you need to do is click OK or press Enter.


Skip Server Type dialog check box (SQL Options dialog box)

Toggle this option On if you want to bypass the Log On Server dialog box altogether.

	When you bypass the Log On Server dialog box, you no longer have the option to activate a non-SQL database for the report or to activate a server other than the default server. Activate this option only if you plan to create all of your reports using a single server.


Server Name text box (SQL Options dialog box)

Enter the name of the SQL server you want to log on to in this edit box.


Database text box (SQL Options dialog box)

Enter the name of the database you want to activate in the specified SQL server in this edit box.


User ID text box (SQL Options dialog box)

Enter the name you want to use to log on to the specified server in this edit box.


Dict. Path text box (SQL Options dialog box)

When using Netware SQL, enter the path for the data dictionary in this edit box.


Data Path text box (SQL Options dialog box)

When using Netware SQL, enter the path for the data files in this edit box.


Tables check box (SQL Options dialog box)

When you toggle this check box On, the program will report on database tables. By default, the Tables check box is toggled On.


Views check box (SQL Options dialog box)

When you toggle this check box On, the program will report on virtual tables. By default, the Views check box is toggled On.


System Tables check box (SQL Options dialog box)

When you toggle this check box On, the program will report on system tables. These tables are typically used by the system administrator only but are available for use if you have the appropriate permissions. By default, the System Tables check box is toggled Off.


Synonyms check box (SQL Options dialog box)

When you toggle this check box On, the program will allow reporting on virtual tables that are available on some hosts. By default, the Synonyms check box is toggled Off.


Stored Procedures check box (SQL Options dialog box)

When you toggle this check box On, the program will allow reporting on the result sets from stored procedures if you are using SQL systems that support stored procedures.


Table name LIKE text box (SQL Options dialog box)

This text box enables you to enter the SQL LIKE function to specify the kinds of table names you want to appear in the Crystal Query Designer. You can use the underscore character (_) or the percent sign character (%) as wildcards with this function. The underscore character specifies any single character while the percent sign signifies any character string. For example, DAV_ matches DAVE only, while DAV% matches DAVE and DAVID.

Table name LIKE C% would display only those tables that have a table name beginning with the letter C.


Owner LIKE text box (SQL Options dialog box)

This text box works exactly like Table name LIKE except the LIKE function is used to select the Owner (or Creator or Alias) of the table, not the table name itself. For example Owner LIKE C% would display only those tables that had an owner beginning with the letter C.


Prompt on every table check box (SQL Options dialog box)

When you toggle this check box On, the program will require that you verify each table to be accessed while creating your query.


Table Description dialog box

The Table Description dialog box is primarily a read-only dialog box that provides information on any table displayed in either the Links Tab or the Choose Tables To Use In Visual Linking dialog box.

•	To call up the dialog box from the Links Tab, double-click any field in the table of interest or right-click the field and then choose Description from the shortcut menu that appears.

•	To call up the dialog box from the Choose Tables To Use In Visual Linking dialog box, highlight the table of interest and click the Table Description button.

Use the Table Description dialog box to get detailed information on the fields and indexes in the highlighted table.

  Click the area of interest on the picture below for further information:

  Dialog Box Index


Indexes section (Table Description dialog box)

This section contains two list boxes and an Add and a Delete button.

•	The box on the left indicates all the indexes that the program has identified as being connected to the highlighted table. You can highlight any index in this box by clicking the index.

•	The box on the right indicates those fields that are components of the index currently highlighted in the box on the left.


Fields section (Table Description dialog box)

This section describes the following about a highlighted field:

•	if the field is part of an index,

•	the field description, and

•	the field data type and length.

The fields section contains two list boxes and a Browse button.

•	The box on the left indicates all the fields in the highlighted table. You can highlight any field in this box by clicking on the field name.

•	The box on the right gives a description of the field and indicates if it is a component of any index.

When you click the Browse button, the Browse Data dialog box appears indicating the name and source of the highlighted field, the field type, and the field length. It also displays a list of values in the highlighted field.


Record Selection Options

These conditions appear whenever Select Records is used with number, currency, string, or memo fields.

is any value

Select records that have any value in the selected field. Use this option to include all records in the query. No records are excluded.

is equal to

Select records for which the value in the selected field is equal to another value to be specified.

Customer is equal to Acme

«Include all records that show Acme as the customer.»

OrderDate is equal to January 15, 1992

«Include all records that show an order date of January 15, 1992.»

is less than

Select records for which the value in the selected field is less than another value to be specified.

Quantity is less than 5

«Include all records that show a quantity smaller than five.»

Amount is less than 1000.00

«Include all records that show an amount smaller than 1000.00.»

is greater than

Select records for which the value in the selected field is greater than another value to be specified.

Quantity is greater than 5

«Include all records that show a quantity bigger than 5.»

Amount is greater than 1000.00

«Include all records that show an amount bigger than 1000.00.»

is between

Select records for which the value in the selected field falls between or matches one of two values to be specified.

OrderDate is between January 1, 1992 and March 31, 1992

«Include all records in which the order date falls between (or matches either) January 1, 1992 and March 31, 1992.»

ZIP is between 90000 and 99999

«Include all records in which the ZIP code falls between (or matches either) 90000 and 99999.»

is one of

Select records for which the value in the selected field is one of two or more values to be specified.

OrderDate is one of January 15, 1992, or January 16, 1992

«Include all records in which the order date is either January 15, 1992, or January 16, 1992.»

ZIP is one of 85201 or 85202 or 85203.

«Include all records in which the ZIP code is either 85201, 85202, or 85203.»

When you make this selection, the program expands the dialog box and includes a selection text box at the bottom.

Date conditions

If Select Records is used on a date field, all of the primary condition options will appear plus the following two options:

is in the period

Select records for which the value in the selected field falls within the date range specified. When you select this condition, the dialog box displays a scroll list of all Query Designer date conditions. Select the condition you want from the list.

header.DATE is in the period Calendar1stQtr

«Include all records in which the date falls within the calendar first quarter of the year. Dates from January 1 to April 30 (including January 1 and April 30) will be included; all other dates will be excluded.»

is not in the period

Select records for which the value in the selected field does not fall within the date range specified. When you select this condition, the dialog box displays a scroll list of all Query Designer date conditions. Select the condition you want from the list.

header.DATE is not in the period Calendar1stQtr

«Include all records in which the date falls outside the calendar first quarter of the year. Dates from January 1 to April 30 (including January 1 and April 30) will be excluded, all other dates will be included.»

Boolean conditions

If Select Records is used on a Boolean field, your condition options will include only is any value and satisfies the test below from the primary condition list above plus the following two options:

is True

Select records for which the value in the selected field is true.

file.REGISTERED is True

«Include all records in which the value in the file.REGISTERED field is True.»

is False

Select records for which the value in the selected field is false.

file.REGISTERED is False

«Include all records in which the value in the file.REGISTERED field is false.»


SQL Topics

  Click the topic of interest from the following list for further information:

SELECT Clause

DISTINCT clause

FROM Clause

GROUP BY Clause

WHERE Clause

ORDER BY Clause

HAVING Clause

SQL Expressions

Arithmetic Expressions

Numeric Functions

Time and Date Functions

String Functions

Aggregate (Summary) Functions

ODBC SQL Functions

SQL Join Types

  The Crystal SQL Designer Help Main Index


SELECT Clause

The SELECT clause indicates specific data items to retrieve from database tables. The item retrieved may be the values in a database field (column) or it may be the result of a calculation performed while gathering the data. For example:

SELECT
TABLEA.'CUSTNAME',
TABLEA.'STATE'

  SQL Topics


DISTINCT clause

The DISTINCT clause can be added to an SQL statement just after the SELECT clause. DISTINCT forces the query to retrieve only unique (distinct) sets of data. A row of results will only be retrieved once. A SELECT statement can be changed to use the DISTINCT clause:

SELECT DISTINCT
TABLEA.'CUSTNAME',
TABLEA.'STATE'

  SQL Topics


FROM clause

The FROM clause specifies the sources of the database fields indicated in the SELECT clause. FROM lists actual database tables that contain the fields and records containing the requested data. The FROM clause generated by the program precedes the name of each table with the alias it uses to identify the table in your query. The following illustrates the FROM clause with the SELECT clause:

SELECT
TABLEA.'CUSTNAME',
TABLEA.'STATE'
FROM
'TABLEA' TABLEA

  SQL Topics


WHERE clause

The WHERE clause has two purposes:

1.	WHERE can specify record selection criteria.

2.	WHERE can specify how two database tables are joined.

When WHERE is used to specify record selection criteria, it specifies a search condition to use to determine which records (rows of data) are to be retrieved. For example:

SELECT
MYTABLE.'SALESPERSON',
MYTABLE.'SALESTOTAL'
FROM
'MYTABLE' MYTABLE
WHERE
MYTABLE.'SALESTOTAL' < 10000.00

If WHERE is used to specify how two tables are linked, an SQL join operator sits between the two table names. The following is an example of the WHERE clause joining two tables:

SELECT
CUSTOMER.'CUST_ID',
CUSTOMER.'CUST_NAME',
ORDERS.'AMOUNT'
FROM
'CUSTOMER' CUSTOMER,
'ORDERS' ORDERS
WHERE
CUSTOMER.'CUST_ID' = ORDERS.'CUST_ID'

  SQL Topics


ORDER BY clause

The ORDER BY clause indicates that the database records retrieved should be sorted according to the values in a specific field. If the ORDER BY clause is not used, records are retrieved in the order that they appear in the original database. If more than one field is specified after the ORDER BY clause, the records are sorted according to the values in the first field specified, then, within that sort, they are sorted by the values in the second field, specified, and so on. The following SQL statement uses the ORDER BY clause:

SELECT
MYTABLE.'COMPANY',
MYTABLE.'CITY',
MYTABLE.'STATE'
FROM
'MYTABLE' MYTABLE
ORDER BY
MYTABLE.'STATE' ASC,
MYTABLE.'CITY' ASC

	ASC indicates that the values in the field are sorted in ascending order rather than descending order (DESC). Ascending order sorts letters from A to Z and numbers from 0 to 9.
  SQL Topics


GROUP BY clause

The GROUP BY clause retrieves a set of summary data. Instead of retrieving the data itself, GROUP BY groups the data and summarizes each group according to an SQL aggregate function. Only the summarization information for each group is returned to the program. For example:

SELECT
MYTABLE.'STATE',
MYTABLE.'ZIPCODE',
SUM (MYTABLE.'SALES')

FROM
'MYTABLE' MYTABLE
GROUP BY
MYTABLE.'STATE',
MYTABLE.'ZIPCODE'

  SQL Topics


HAVING clause

The HAVING clause creates selection criteria for the summary information produced by the GROUP BY clause. The function of the HAVING clause is similar to the record selection function of the WHERE clause, but HAVING applies to summary data produced by the GROUP BY clause. For this reason, you will always see a GROUP BY clause just before the HAVING clause in any of the program's SQL statements that produce summary results. This can be seen in the following example:

SELECT
TABLE.'CUSTOMER',
SUM (TABLE.'ORDERAMOUNT')
FROM
'TABLE' TABLE
GROUP BY
TABLE.'CUSTOMER'
HAVING
SUM (TABLE.'ORDERAMOUNT') > 20000.00

  SQL Topics


SQL Expressions

	To create an SQL Expression, click the Expression button on the Fields Tab.
SQL Expressions can be used to take advantage of some of the more advanced features of SQL. An SQL expression is formed by applying operators or functions to database fields or other SQL expressions. The two most common ways of forming an SQL Expression are:

FUNCTION(Table.`fieldname`)

«Where FUNCTION is either an Aggregate function or another built in function.»

Table.`fieldname` [arithmetic operator] Table.`fieldname`.

An SQL expression can use database fields along with:

•	Arithmetic Expressions - Allowing you to add, multiply, divide, or subtract between any number of fields and/or other numeric expressions.

•	Aggregate, Total or Summary Functions - Allowing you to summarize field data built into the Query Designer on the Fields Tab.

•	ODBC SQL Functions - Including various string, numeric and date/time functions.

  How to create an SQL expression


Arithmetic Expressions

Arithmetic Expressions have the following syntax:

(ExpressionA) [operator] (ExpressionB)

«Where ExpressionA and ExpressionB can be any other expression, field name, or numeric value. The [operator] must be an arithmetic operator either +, -, * or / for addition, subtraction, multiplication and division.»

For example, if you wanted to find out a total for an order line item, the English description of it would be quantity times price and the SQL Expression would be:

Orders.`Quantity`*Orders.`Price`

Or, if you wanted to calculate a 1.5% commission based on that same total, the expression would be:

Orders.`Quantity`*Orders.'Price'*.015

  SQL Topics


Aggregate (Summary) Functions

These are the five summary functions available to you in the Total drop-down list of the Fields Tab. You can also use these functions to create an SQL expression in the SQL Tab. Aggregate functions include SUM, AVG, MIN, MAX and COUNT.

•	SUM
adds all values in a field together. It can only be used on numeric values.

•	AVG
calculates the average (mean) of all values in a field. It can only be used on numeric values.

•	MIN
finds the minimum (lowest) value of all values in a field.

•	MAX
finds the maximum (highest) value of all values in a field.

•	COUNT
counts the number of values in the field.

A GROUP BY clause is added to the SQL Statement. This essentially sorts your query into meaningful groups. Generally, the Create SQL Expert will use the first field or expression in the query.

Whenever a GROUP BY statement is used in conjunction with an aggregate function, the SQL statement returns only one value for each unique field value in the field used to group by.

These individual values then become part of a row in the query that includes any other fields you have added, along with the value that the aggregate function has calculated on all the values in the group.

The field that the function is applied to appears in the SQL statement on the SQL Tab inside the aggregate function. For example: SUM(table.FIELD).

The field that the aggregate function was applied to is disqualified as a field that can be sorted on in the Sort Tab. However, the summarized field can be selected as a select field on the Select Tab. See How to select groups to be included for more information.

  SQL Topics


ODBC/SQL Functions

ODBC SQL grammar provides many additional functions that do not exist in standard ANSI SQL. Since the Query Designer accesses data through ODBC, it fully supports the ODBC SQL set of functions. For a small subset of the functions available, click one of the topics below. For more information on ODBC and the ODBC SQL language, refer to the Microsoft ODBC Software Development Kit.

  How to create an SQL expression

  Time and Date Functions

  String Functions

  Numeric Functions


Time and Date Functions

These ODBC SQL functions operate on time and date values:

SECOND(time_expression)	Returns the seconds of a time expression
HOUR(time_expression)	Returns the hour of a time expression
MINUTE(time_expression)	Returns the minute of a time expression
CURDATE()	Returns the current system date
CURTIME()	Returns the current system time
DAYNAME(date_expression)	Returns the name of the day of the date expression
MONTH(date_expression)	Returns the number of the month of the date expression
NOW()	Returns the current system date and time
  SQL Topics

  How to create an SQL expression

  String Functions

  Numeric Functions


String Functions

These ODBC SQL functions operate on any string expression, generally either a string field or a string surrounded by quotation marks.

ASCII(string_expression)	Returns the ASCII code
CHAR(code)	Returns the character of an ASCII code
SPACE(count)	Returns a string of count spaces
LENGTH(string_expression)	Returns the length of a string
  SQL Topics

  How to create an SQL expression

  Time and Date Functions

  Numeric Functions


Numeric Functions

There are many ODBC numeric functions. Listed below are just a few. Please consult an ODBC reference manual for more information.

PI()	Returns the value of PI
EXP(float_expression)	Returns the exponential value of a float-point #
FLOOR(numeric_expression)	Returns next lower integer
SIN,COS,ACOS,ATAN(float_expression)	Standard Trig functions
  SQL Topics

  How to create an SQL expression

  String Functions

  Time and Date Functions


SQL join types (ODBC data sources)

The Crystal Query Designer enables you to specify the type of join you want to use when SQL tables are linked. An SQL join indicates how linked fields in two SQL tables are compared when records are read. The SQL Join Type options are specified in the Crystal Query Designer using the Link Options dialog box.

	When linking using SQL joins, no indexed fields are required.
  Click the topic of interest from the following list for further information:

•	Equal join [=]

•	Left outer join [=(+), *=]

•	Right outer join [(+)=, =*]

•	Greater Than join [>]

•	Less Than join [<]

•	Greater or Equal join [>=]

•	Less or Equal join [<=]

•	Not Equal join [!=, <>]

	For this example, each join is specified in the WHERE clause of the SQL statement.


Equal to [=] join
Equal [=] join

	For this example, each join is specified in the WHERE clause of the SQL statement.
The result set from an Equal join includes all the records where the linked field value in both tables is an exact match. In the following example, the Customer table is linked to the Orders table by the Customer ID field. When a Customer ID is found in the Orders table that matches a Customer ID in the Customer table, information is displayed for the corresponding records in both tables.

SQL uses the following syntax to describe an Equal join:

SELECT Customer.'Customer ID',
Customer.'Customer Name',
Orders.'Order Amount'
FROM 'Customer' Customer,
'Orders' Orders
WHERE Customer.Customer ID =
Orders.Customer ID

This statement produces the following data:

Customer Table Customer ID	Customer Table Customer Name	Orders Table Order Amount
52	Allez Distribution	25141.50
53	BG Mountain Inc.	19164.30
53	BG Mountain Inc.	1683.60
57	Hansen MTB Inc.	15716.40
58	La Bomba Bicicleta	1956.20
60	Mountain Toad	24580.50
62	SFB Inc.	7911.80
63	Sierra Bicycle Group	19766.20
63	Sierra Bicycle Group	12763.95
64	Sierra Mountain	8233.50
  SQL join types (ODBC data sources)

  Not Equal join


Left Outer [=(+), *=] join

	For this example, each join is specified in the WHERE clause of the SQL statement.
The result set from a Left Outer join includes all the records where the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table whose linked field value has no match in the lookup table. For instance, you can use a Left Outer join to view all customers and the orders they have placed, but you also get a row for every customer that has not placed any orders. These customers appear at the end of the list with blanks in the fields that would hold order information:

Customer Table Customer ID	Customer Table Customer Name	Orders Table Order Amount
52	Allez Distribution	25141.50
53	BG Mountain Inc.	19164.30
53	BG Mountain Inc.	1683.60
57	Hansen MTB Inc.	15716.40
58	La Bomba Bicicleta	1956.20
60	Mountain Toad	24580.50
62	SFB Inc.	7911.80
63	Sierra Bicycle Group	19766.20
63	Sierra Bicycle Group	12763.95
64	Sierra Mountain	8233.50
54	Bicicletas Aztecas
55	Deely MTB Inc.
Left Outer and Right Outer joins are handled differently in the SQL language from other join types. If the database is accessed through ODBC, the Crystal Query Designer will use ODBC syntax in the SQL statement. If you are connecting to an SQL database directly (not through ODBC), the Crystal Query Designer will use a syntax native to the database. For complete information on what an Outer join looks like in an SQL statement, refer to Microsoft ODBC documentation or the documentation for your SQL database.

  SQL join types (ODBC data sources)

  Right Outer join


Right Outer [(+)=, =*] join

	For this example, each join is specified in the WHERE clause of the SQL statement.
The result set from a Right Outer join includes all the records where the linked field value in both tables is an exact match. It also includes a row for every record in the lookup (right) table whose linked field value has no match in the primary table. If you link the Customer table to the Orders table, you get a row in the table for every order a customer has placed, just like an Equal join. You also get a row for every order found that can not be linked to a customer. Theoretically, this should not happen, but if an inexperienced sales person forgot to assign a customer ID to an order, you can quickly locate that order with a Right Outer join. The resulting table leaves a blank in any Customer fields for the order without a customer:

Customer Table Customer ID	Orders Table Order ID	Orders Table Order Amount
52	6	25141.50
53	11	19164.30
53	21	1683.60
57	4	15716.40
58	20	1956.20
60	16	24580.50
62	19	7911.80
63	28	19766.20
63	32	12763.95
64	14	8233.50
	25	10320.87
Left Outer and Right Outer joins are handled differently in the SQL language from other join types. If the database is accessed through ODBC, the Crystal Query Designer will use ODBC syntax in the SQL statement. If you are connecting to an SQL database directly (not through ODBC), the Crystal Query Designer will use a syntax native to the database. For complete information on what an Outer join looks like in an SQL statement, refer to Microsoft ODBC documentation or the documentation for your SQL database.

  SQL join types (ODBC data sources)

  Left outer join


Greater Than [>] join

	For this example, each join is specified in the WHERE clause of the SQL statement.
The result set from a Greater Than join includes all records in which the linked field value from the primary table is greater than the linked field value in the lookup table. As an example, a company may want to compare the salaries made by all of their sales representatives to the salaries made by all of their sales managers. The company executives want to make sure no sales representative is making more money than any manager. With this in mind, you can link the SalesRep table to the Manager table, by the Salary field in each table, using a Greater Than join:

SELECT SalesRep.'Last Name',
SalesRep.'Salary',
Manager.'Last Name',
Manager.'Salary'
FROM 'SalesRep' SalesRep,
'Manager' Manager
WHERE SalesRep.'Salary' >
Manager.'Salary'

This SQL statement might produce data similar to this:

SalesRep Table Last Name	SalesRep Table Salary	Manager Table Last Name	Manager Table Salary
Davolio	$35,000.00	Fuller	$32.000.00
Davolio	$35,000.00	Brid	$30,000.00
Davolio	$35,000.00	Buchanan	$29,500.00
Dodsworth	$48,000.00	Hellstern	$45,000.00
Dodsworth	$48,000.00	Fuller	$32,000.00
Dodsworth	$48,000.00	Brid	$30,000.00
Dodsworth	$48,000.00	Buchanan	$29,500.00
Dodsworth	$48,000.00	Martin	$35,000.00
Patterson	$30,000.00	Buchanan	$29,500.00
In this table, there is no relationship between sales representatives and sales managers established. Since all managers have seniority over all sales representatives, a company might find it necessary to check if any representatives make more money than any managers, evidence of a salary problem that needs to be remedied.

  SQL join types (ODBC data sources)

  Less Than join


Less Than [<] join

	For this example, each join is specified in the WHERE clause of the SQL statement.
The result set from a Less Than join includes all records in which the linked field value from the primary table is less than the linked field value in the lookup table. Using the Less Than join, you can compare sales representative and manager salaries in a different direction. Once again, the Salary field in each table is used as the link field. This time, though, link from the Manager table to the SalesRep table using a Less Than join on the linked Salary fields:

SELECT Manager.'Last Name',
Manager.'Salary',
SalesRep.'Last Name',
SalesRep.'Salary'
FROM 'Manager' Manager',
SalesRep' SalesRep
WHERE Manager.'Salary' <
SalesRep.'Salary'

This SQL statement produces a slightly different table than the Greater Than join:

Manager Table Last Name	Manager Table Salary	SalesRep Table Last Name	SalesRep Table Salary
Fuller	$32.000.00	Davolio	$35,000.00
Fuller	$32,000.00	Dodsworth	$48,300.00
Brid	$30,000.00	Davolio	$35,000.00
Brid	$30,000.00	Dodsworth	$48,300.00
Buchanan	$29,500.00	Davolio	$35,000.00
Buchanan	$29,500.00	Dodsworth	$48,300.00
Buchanan	$29,500.00	Patterson	$30,000.00
Martin	$35,000.00	Dodsworth	$48,300.00
Hellstern	$45,000.00	Dodsworth	$48,300.00
  SQL join types (ODBC data sources)

  Greater Than join


Greater Than Or Equal [>=] join

	For this example, each join is specified in the WHERE clause of the SQL statement.
The result set from a Greater Than Or Equal join includes all records in which the linked field value from the primary table is greater than or equal to the linked field value in the lookup table. The example here is identical to the example for the Greater Than join, but it uses the Greater Than Or Equal join:

SELECT SalesRep.'Last Name',
SalesRep.'Salary',
Manager.'Last Name',
Manager.'Salary'
FROM 'SalesRep' SalesRep,
'Manager' Manager
WHERE SalesRep.'Salary' >=
Manager.'Salary'

This statement might produce data such as this:

SalesRep Table Last Name	SalesRep Table Salary	Manager Table Last Name	Manager Table Salary
Davolio	$35,000.00	Fuller	$32.000.00
Davolio	$35,000.00	Brid	$30,000.00
Davolio	$35,000.00	Buchanan	$29,500.00
Davolio	$35,000.00	Martin	#35,000.00
Dodsworth	$48,000.00	Hellstern	$45,000.00
Dodsworth	$48,000.00	Fuller	$32,000.00
Dodsworth	$48,000.00	Brid	$30,000.00
Dodsworth	$48,000.00	Buchanan	$29,500.00
Dodsworth	$48,000.00	Martin	$35,000.00
Patterson	$30,000.00	Brid	$30,000.00
Patterson	$30,000.00	Buchanan	$29,500.00
  SQL join types (ODBC data sources)

  Less Than or Equal join


Less Than Or Equal [<=] join

	For this example, each join is specified in the WHERE clause of the SQL statement.
The result set from a Less Than Or Equal join includes all records in which the linked field value from the primary table is less than or equal to the linked field value in the lookup table. The example here is identical to the example for the Less Than join, but it uses the Less Than Or Equal join:

SELECT Manager.'Last Name',
Manager.'Salary',
SalesRep.'Last Name',
SalesRep.'Salary'
FROM 'Manager' Manager',
SalesRep' SalesRep
WHERE Manager.'Salary' <=
SalesRep.'Salary'

This SQL statement produces data like the following:

Manager Table Last Name	Manager Table Salary	SalesRep Table Last Name	SalesRep Table Salary
Fuller	$32.000.00	Davolio	$35,000.00
Fuller	$32,000.00	Dodsworth	$48,300.00
Brid	$30,000.00	Davolio	$35,000.00
Brid	$30,000.00	Dodsworth	$48,300.00
Brid	$30,000.00	Patterson	$30,000.00
Buchanan	$29,500.00	Davolio	$35,000.00
Buchanan	$29,500.00	Dodsworth	$48,300.00
Buchanan	$29,500.00	Patterson	$30,000.00
Martin	$35,000.00	Davolio	$35,000.00
Martin	$35,000.00	Dodsworth	$48,300.00
Hellstern	$45,000.00	Dodsworth	$48,300.00
  SQL join types (ODBC data sources)

  Greater Than or Equal join


Not Equal [!=, <>] join

	For this example, each join is specified in the WHERE clause of the SQL statement.
The result set from a Not Equal join includes all records in which the linked field value from the primary table is not equal to the linked field value in the lookup table. This type of join can be used to find possible combinations of items when a table is joined to itself (a self-join). For example, a company can have a table listing all products they sell. When they decide to hold a sale where their customers buy one item and get the second item half price, they may need a list of all possible two item combinations:

SELECT Product1.'Product Name',
Product2.'Product Name',
FROM 'Product' Product1
'Product' Product2
WHERE Product1.'Product Name' !=
Product2.'Product Name'

In this SQL statement, the Product table is opened twice. The first time, it is given the alias name Product1. The second time, it is given the alias name Product2. Then, the Product Name field is used to link from the Product1 table to the Product2 table. This is the same table, but since it has been opened twice using different aliases, the Crystal Query Designer will consider it as two separate tables. A Not Equal join is used to link the tables by the Product Name field. As a result, each product is paired with every other product offered, but is not paired with itself:

Product1 Product Name	Product2 Product Name
Craze Adult Helmet	Craze Mtn. Lock
Craze Adult Helmet	InFlux Lycra Glove
Craze Adult Helmet	Radster Micro Mtn Saddle
Craze Mtn. Lock	Craze Adult Helmet
Craze Mtn. Lock	InFlux Lycra Glove
Craze Mtn. Lock	Roadster Micro Mtn Saddle
InFlux Lycra Glove	Craze Adult Helmet
InFlux Lycra Glove	Craze Mtn. Lock
InFlux Lycra Glove	Roadster Micro Mtn Saddle
Roadster Micro Mtn Saddle	Craze Adult Helmet
Roadster Micro Mtn Saddle	Craze Mtn Lock
Roadster Micro Mtn Saddle	InFlux Lycra Glove
	The symbol != is used to represent a Not Equal join if the ODBC data source driver for the data being accessed supports this symbol. If not, the default symbol <> is used to represent a Not Equal join.
  SQL join types (ODBC data sources)

  Equal join